
from config import *
from sql import sqlseverDB
from tool import ask_deepseek,ask_deepseek_json
import requests,datetime,re
import logging,traceback,json,time,dirtyjson
import concurrent.futures as thddd

tms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)

def task_ads_title_breakdown():

    utc_plus_7 = datetime.timezone(datetime.timedelta(hours=7))
    now = datetime.datetime.now(utc_plus_7)
    cur_year=now.year
    cur_m=now.month
    cur_date=f'{cur_year}-{cur_m}-01'

    sql_str1=f'''select itemid,title,campaign_id,bid,bname,shopid,purl,sjbm,yybm,country,random_code
                from ShopeeAdsReport p where dtype='yesterday' and state='ongoing'
                and not EXISTS (SELECT 1 FROM AdsTitle a where  a.itemid=p.itemid)
            '''
    rs_ads=tms.ExecQuery(sql_str1)
    aff_tit=0
    aff_tit_ign=0
    logging.info(f'第一步,从表《ShopeeAdsReport》新增广告标题...')
    for aditem in rs_ads:
        itemid,title,campaign_id,bid,bname,shopid,purl,sjbm,yybm,country,random_code=aditem
        rs_has=tms.ExecQuery('select count(*) from AdsTitle where itemid=?',(itemid,))
        if rs_has[0][0]==0:
            pdata={
                'itemid':itemid,
                'campaign_id':campaign_id,
                'title':title,
                'random_code':random_code,
                'purl':purl,
                'bid':bid,
                'bname':bname,
                'shopid':shopid,
                'country':country,
                'sjbm':sjbm,
                'yybm':yybm
            }

            zds=list(pdata.keys())
            paras=list(pdata.values())
            aff_tit+=tms.merge_into('AdsTitle',zds,paras,['itemid'],True)
        else:
            aff_tit_ign+=1
    
    logging.info(f'需存入:{len(rs_ads)} 个广告标题,成功:{aff_tit},已存在忽略:{aff_tit_ign}')
    
    time.sleep(1)
    rs_adtit=tms.ExecQuery('select itemid,title from AdsTitle where title_cn is null')
    logging.info(f'第二步,将表《AdsTitle》 {len(rs_adtit)} 个标题翻译为中文标题...')
    session=requests.session()
    session.headers=DS_Headers
    for rj,r in enumerate(rs_adtit,start=1):
        cur_itemid,cur_title=r
        logging.info(f'第{rj}个itemid:{cur_itemid},原标题长度:{len(cur_title)},开始翻译为中文...')
        if cur_title.endswith('|'):
            cur_title=cur_title[:-10]        
        question=f'{cur_title},帮我翻译成中文.只回答我翻译的结果即可'
        cn_title=ask_deepseek(session,question)
        if cn_title:
            aff_cn=tms.ExecNoQuery('update AdsTitle set title_cn=? where itemid=?',(cn_title,cur_itemid))
            logging.info(f'第{rj}个itemid:{cur_itemid},翻译结果长度:{len(cn_title)},保存状态:{aff_cn}')

        else:
            logging.info(f'第{rj}个itemid:{cur_itemid},翻译失败')
        
        logging.info(f'剩余翻译队列:{len(rs_adtit)-rj}/{len(rs_adtit)}')
    time.sleep(1)
    rs_cntit=tms.ExecQuery('select itemid,title_cn,country from AdsTitle where title_cn is not null and is_get_kw=0')
    logging.info(f'第二步,将表《AdsTitle》 {len(rs_adtit)} 个已翻译中文标题拆解...')

    for rcj,rc in enumerate(rs_cntit,start=1):
        cur_itemid,cur_cntitle,cur_country=rc
        logging.info(f'第{rcj}个itemid:{cur_itemid},标题长度:{len(cur_cntitle)},开始拆解...')
        question2=f'''{cur_cntitle}。
提取上面中文产品标题的的所有品名词和属性词，纯数字的词不要,需要各个词在标题中的位置，标题分前、中、后三段位置。
回答结果就可以，返回要求JSON字符串,格式为 [{{keyword:"苹果","type":"品名词","location":"前"}},{{keyword:"白色","type":"属性","location":"中"}}]'''
        
        cot=ask_deepseek(session,question2)
        is_suc=False
        if cot:
            try:
                kwlist=list(dirtyjson.loads(cot))
                if kwlist:
                    is_suc=True
            except Exception as e:
                print(e)
        
        if is_suc:
            aff_save=0
            aff_zt=0
            for kwitem in kwlist:
                kwtype=kwitem['type']

                if not kwtype.endswith('词'):
                    kwtype=kwtype+'词'
                kdata={
                    'itemid':cur_itemid,
                    'keyword':kwitem['keyword'],
                    'type':kwtype,
                    'words_number':1,
                    'words_location':kwitem['location'],
                    'country':cur_country
                }
                kzds=list(kdata.keys())
                kparas=list(kdata.values())
                aff_save+=tms.merge_into('AdsKeyword',kzds,kparas,['itemid','keyword','type'],True)
            if aff_save:
                aff_zt=tms.ExecNoQuery('update AdsTitle set is_get_kw=1 where itemid=?',(cur_itemid,))
            logging.info(f'第{rcj}个itemid:{cur_itemid},成功拆解了 {len(kwlist)} 个单词,保存数量:{aff_save},更改状态:{aff_zt}')

        else:
            logging.info(f'第{rcj}个itemid:{cur_itemid},拆解失败')
        
        logging.info(f'剩余翻译队列:{len(rs_cntit)-rcj}/{len(rs_cntit)}')
            
def task_get_peripheral_products():

    sql_str1=f'''WITH agg AS (
                    SELECT 
                        keyword,
                        MAX(keyword_type) AS keyword_type,
                        MAX(is_cj_zb) AS max_is_cj_zb,
                        MIN(gender) AS min_gender
                    FROM keyword_1688
                    GROUP BY keyword
                )
                SELECT top 1000 keyword,min_gender
                FROM agg p
                WHERE max_is_cj_zb = 0
                AND NOT EXISTS (
                    SELECT 1
                    FROM Blacklist b
                    WHERE b.word_language = '中文'
                    AND p.keyword LIKE '%' + b.word_blacklist + '%'
                ) order by min_gender
            '''
    rs_kws=tms.ExecQuery(sql_str1)
    session=requests.session()
    session.headers=DS_Headers
    logging.info(f'本次共取出 {len(rs_kws)} 个关键词进行采集周边词')
    for rj,r in enumerate(rs_kws,start=1):
        yskw,ys_gender=r
        logging.info(f'第{rj}个关键词:{yskw},开始采集周边...')    
        question=f'关键词：{yskw},它的强关联周边产品推荐20个，回答产品名字即可,不要加符号,一行一个不需要序号'
        res_str=ask_deepseek(session,question)
        if res_str:
            aff_cj=0
            aff_ig=0
            aff_change_state=0
            cjkw_list = [x.strip() for x in res_str.strip().split('\n') if x.strip()]
            for cj_kw in cjkw_list:
                cj_kw=cj_kw.strip('（）《》<>[]()【】 ')
                rs_ck=tms.ExecQuery('select count(*) from keyword_1688 where keyword=? and emarket=?',(cj_kw,'shopee马来'))
                if rs_ck[0][0]==0:
                    kdata={
                        'keyword':cj_kw,
                        'emarket':'shopee马来',
                        'keyword_type':'广泛匹配关键词',
                        'from_keyword':yskw,
                        'gender':4
                    }
                    zds=list(kdata.keys())
                    paras=list(kdata.values())
                    aff_cj+=tms.merge_into('keyword_1688',zds,paras,['keyword','emarket'],True)
                else:
                    aff_ig+=1
            if cjkw_list:
                aff_change_state=tms.ExecNoQuery('update keyword_1688 set is_cj_zb=1 where keyword=?',(yskw,))
            logging.info(f'第{rj}个关键词:{yskw},采集周边产品:{len(cjkw_list)},保存:{aff_cj},忽略:{aff_ig},更改状态:{aff_change_state}')

        else:
            logging.info(f'第{rj}个关键词:{yskw},采集周边失败')
        
        logging.info(f'本次剩余采集队列:{len(rs_kws)-rj}/{len(rs_kws)}')
    
def task_get_cpc_and_risk():

    sql_str1=f'''select itemid,title from ShopeeAdsReport 
                    where dtype='last_month' 
                    and yybm='mylxh999' 
                    and click > 4 
                    and ctr > 0.03 
                    and cost/click < 0.11
                    and keyword_status is null
            '''
    rs_tits=tms.ExecQuery(sql_str1)
    session=requests.session()
    session.headers=DS_Headers
    logging.info(f'本次共取出 {len(rs_tits)} 个标题进行提取标题关键词和侵权词')

    for rj,r in enumerate(rs_tits,start=1):
        itemid,title=r
        logging.info(f'第{rj}个itemid:{itemid},开始采集提取标题关键词和侵权词...')    
        question1=f'{title}\n从这个产品名称中提取1个中文品名词和1个中文核心属性词。只回答中文结果即可,两个词用空格隔开,品名词在前属性词在后'
        question2=f'{title}\n从这个标题里面翻译并提取最可能侵权的一个中文词。只回答侵权词中文结果,如果没有侵权词就回答无'
        res_str1=ask_deepseek(session,question1)
        res_str2=ask_deepseek(session,question2)
        if res_str1 and res_str2:
            res_str1=res_str1.strip('（）《》<>[]()【】 ')
            res_str2=res_str2.strip('（）《》<>[]()【】 ')
            aff_change_state=tms.ExecNoQuery('update ShopeeAdsReport set keyword_cpc=?,keyword_risk=?,keyword_status=? where itemid=? and dtype=?',(res_str1,res_str2,'已提取',itemid,'last_month'))
            logging.info(f'第{rj}个itemid:{itemid},提取关键词结果:《{res_str1}》,提取侵权词结果:《{res_str2}》,更改状态:{aff_change_state}')

        else:
            logging.info(f'第{rj}个itemid:{itemid},提取标题关键词和侵权词失败')
        
        logging.info(f'本次剩余采集队列:{len(rs_tits)-rj}/{len(rs_tits)}')

pat_tit=re.compile(r'\|[0-9a-zA-Z]{8}\|$')
def task_get_ml_kw():
    session=requests.session()
    session.headers=DS_Headers
    def ask_ml_tit(jjj,code,ques):
        logging.info(f'第{jjj}个随机码:{code},开始采集提取标题马来关键词...')
        kw_1688=None
        for _ in range(3):
            cot=ask_deepseek(session,ques,'deepseek-reasoner')
            if cot.strip():
                kw_1688=cot.strip()
                break
        
        if kw_1688:
            kw_1688_data={
                'keyword':kw_1688,
                'emarket':'shopee马来',
                'gender':1,
                'keyword_type':'选品关键词',
                'language':'英语'
            }

            rs_cc= tms.ExecQuery('select count(*) from keyword_1688 where keyword=? and language=? and emarket=?',(kw_1688,'英语','shopee马来'))
            if rs_cc[0][0]>0:
                return (1,'提取成功,已存在',jjj,code,kw_1688)
            else:
                zds=list(kw_1688_data.keys())
                paras=list(kw_1688_data.values())
                aff=tms.merge_into('keyword_1688',zds,paras,['keyword','emarket','language'])
                return (aff,'提取成功',jjj,code,kw_1688)
        else:
            return (0,'提取失败',jjj,code,None)


    sql_str1=f'''select distinct title,random_code  from ShopeeAdsReport 
                    where yybm in ('mylxh999','my9999') 
                    and click>4 
                    and ctr>0.029 
                    and cost/click <0.11 
                    and keyword_cpc_my 
                    is null
            '''
    rs1=tms.ExecQuery(sql_str1)
    adatas=[]
    in_1688_count=0
    notin_1688_count=0
    for stit,rcode in rs1:

        rs2= tms.ExecQuery('select mltitle from S1688Pro where random_code=?',(rcode,))
        if len(rs2)>0 and rs2[0][0]:
            adatas.append((rcode,rs2[0][0]))
            in_1688_count+=1
        else:
            atit=pat_tit.sub('',stit).strip()
            adatas.append((rcode,atit))
            notin_1688_count+=1
    logging.info(f'本次共取出 {len(rs1)} 个马来标题,其中在1688 {in_1688_count} 个,不在1688 {notin_1688_count} 个')
    with thddd.ThreadPoolExecutor(max_workers=10) as t:
        
        tasks=[]
        for rj,adata in enumerate(adatas,start=1):
            cur_rcode,cur_title=adata
            question1=f'{cur_title}\n请用英语提取标题中的1个主要产品品名词和1个主要属性词。只需结果两个词用空格隔开，正常拼写，无需其它文字'
            tasks.append(t.submit(ask_ml_tit,rj,cur_rcode,question1))
        for t0 in thddd.as_completed(tasks):
            res=t0.result()

            if res:
                res_aff,res_msg,res_jjj,res_rcode,res_kw=res
                if res_aff:
                    aff_change_state=tms.ExecNoQuery('update ShopeeAdsReport set keyword_cpc_my=? where random_code=?',('已提取',res_rcode))
                    logging.info(f'第{res_jjj}个随机码:{res_rcode},提取关键词:《{res_kw}》,状态:{res_msg},保存:{res_aff},更改状态:{aff_change_state}')
                else:
                    logging.info(f'第{res_jjj}个随机码:{res_rcode},提取关键词:《{res_kw}》,状态:{res_msg},保存:{res_aff}')

            logging.info(f'本次剩余采集队列:{len(adatas)-res_jjj}/{len(adatas)}')

def task_map_comments_to_sku():

    rs=tms.ExecQuery("select itemid,cmt_Jlist from ShopeePHADPro where cmt_Jlist is not null and is_cmt_to_sku=0")
    logging.info(f'本次任务队列一共 {len(rs)} 个')
    for cj,r in enumerate(rs,start=1):

        itemid,cmt_Jlist=r
        sku_list=tms.ExecQuery('select skuid,sku1,sku2 from ShopeePHADProSKU where itemid=?',(itemid,))
        if len(sku_list)>0:
            cmt_list=json.loads(cmt_Jlist)
            logging.info(f'第{cj}个itemid:{itemid},开始解析 {len(cmt_list)} 个评论到 {len(sku_list)} 个sku')
            if len(sku_list)==1 and sku_list[0][1]=='one style':
                aff_change_state=0
                aff_sku=tms.ExecNoQuery('update ShopeePHADProSKU set cmt_count=? where itemid=? and skuid=?',(len(cmt_list),itemid,sku_list[0][0]))
                if aff_sku:
                    aff_change_state=tms.ExecNoQuery('update ShopeePHADPro set is_cmt_to_sku=1 where itemid=?',(itemid,))
                logging.info(f'第{cj}个itemid:{itemid},成功解析 {len(cmt_list)} 个评论到 skuid:{sku_list[0][0]},保存:{aff_sku},更改状态:{aff_change_state}')

            else:
                sku_map={}
                for skuid,sku1,sku2 in sku_list:
                    cur_map={}
                    cur_map['model_name']=f"{sku1},{sku2}" if sku2 else f"{sku1}"
                    cur_map['cmt_count']=0
                    sku_map[skuid]=cur_map

                not_find_count=0
                for cmt in cmt_list:
                    is_find_sku=False
                    product_items=cmt.get(('product_items'),[])
                    for product_item in product_items:
                        modelid=product_item['modelid']
                        model_name=product_item.get('model_name')
                        if sku_map.get(str(modelid)):
                            sku_map[str(modelid)]['cmt_count']+=1
                            is_find_sku=True
                        else:
                            for k,v in sku_map.items():
                                if v['model_name']==model_name:
                                    sku_map[k]['cmt_count']+=1
                                    is_find_sku=True
                                    break
                    if not is_find_sku:
                        not_find_count+=1
                aff_all_sku=0
                for k,v in sku_map.items():
                    cur_model_name=v['model_name']
                    cur_cmt_count=v['cmt_count']
                    if cur_cmt_count>0:
                        aff_sku=tms.ExecNoQuery('update ShopeePHADProSKU set cmt_count=? where skuid=?',(cur_cmt_count,k))
                        logging.info(f'第{cj}个itemid:{itemid},skuid:{k},model_name:{cur_model_name},评论数:{cur_cmt_count},保存:{aff_sku}')
                        aff_all_sku+=aff_sku

                aff_change_state=0
                if aff_all_sku:
                    aff_change_state=tms.ExecNoQuery('update ShopeePHADPro set is_cmt_to_sku=1 where itemid=?',(itemid,))
                
                logging.info(f'第{cj}个itemid:{itemid},共 {len(cmt_list)} 个评论,成功解析到 {aff_all_sku} 个sku上,未找到sku的评论数:{not_find_count},更改状态:{aff_change_state}')                    



        logging.info(f'当前剩余任务队列:{len(rs)-cj}/{len(rs)}')

def task_create_pain_point_solution():

    def ask_td(jjj,pitem,ques):
        cur_itemid,cur_pname,cur_random_code,cur_country=pitem
        plist=None
        for _ in range(3):
            pcot=ask_deepseek_json(ques,frequency_penalty=0.1)
            if pcot:
                plist=json.loads(pcot)
                break
        
        if plist:
            aff=0
            for pj,pdict in enumerate(plist,start=1):

                kw_1688_data={
                    'product_id':cur_itemid,
                    'platform':'shopee',
                    'random_code':cur_random_code,
                    'country':cur_country,
                    'pain_point_id':pj,
                    'pain_point':pdict['pain_point'],
                    'solution':pdict['solution']
                }

                zds=list(kw_1688_data.keys())
                paras=list(kw_1688_data.values())
                aff+=tms.merge_into('PainPointSolution',zds,paras,['product_id','pain_point_id'])
            
            logging.info(f'第{jjj}个itemid:{cur_itemid},成功生成:{len(plist)} 个痛点,保存:{aff}')
            return (aff,jjj,cur_itemid)
        else:
            return (0,jjj,cur_itemid)


    sql_str1=f'''select itemid,pname,random_code,country from ShopeePHADPro 
                    where is_create_pain_point_solution=0 
                    and cmt_count>4
                    and price_before_discount between 40 and 99 
                    and country='马来西亚'
            '''
    rs1=tms.ExecQuery(sql_str1)
    logging.info(f'本次共取出 {len(rs1)} 个标题,来生成痛点和解决方案')
    with thddd.ThreadPoolExecutor(max_workers=10) as t:
        
        tasks=[]
        for rj,r in enumerate(rs1,start=1):
            itemid,pname,random_code,country=r
            question1=f'''{pname}
生成最多8个该产品的{country}用户痛点和解决方案,所有字段值必须使用中文。解决方案需使用其它产品来解决痛点，越简洁越好,其它产品需符合体积小、重量轻、低成本、非液体。
返回格式:[
{{
    "pain_point":"痛点1...",
    "solution":'解决方案2...'
}},
{{
    "pain_point":"痛点2...",
    "solution":'解决方案2...'
}},
]'''
            
            tasks.append(t.submit(ask_td,rj,r,question1))


        for tjjj,t0 in enumerate(thddd.as_completed(tasks),start=1):
            try:
                res=t0.result()
            except Exception as e:
                traceback.print_exc()
                print(f'线程错误 => {e}')

            if res:
                res_aff,res_jjj,res_itemid=res
                if res_aff:
                    aff_change_state=tms.ExecNoQuery('update ShopeePHADPro set is_create_pain_point_solution=1 where itemid=?',(res_itemid,))
                    logging.info(f'第{res_jjj}个itemid:{res_itemid},保存:{res_aff},更改状态:{aff_change_state}')
                else:
                    logging.info(f'第{res_jjj}个itemid:{res_itemid},保存:{res_aff}')


            logging.info(f'本次剩余任务队列:{len(rs1)-tjjj}/{len(rs1)}')

def task_extract_1688_size_weight_type():

    def ask_td(jjj,ykw,ques):
        json_result=None
        return_td={'code':0,'err_msg':None,'ykw':ykw,'kw_s_w_type':None,'aff':0,'ind':jjj}
        for _ in range(3):
            res_cot=ask_deepseek_json(ques)
            if res_cot:
                try:
                    json_result=json.loads(res_cot)
                    kw_size=json_result['体积']
                    kw_weight=json_result['重量']
                    kw_s_w_type=f'体积{kw_size},重量{kw_weight}'
                    aff=tms.ExecNoQuery('update keyword_1688 set size_weight_type=? where keyword=?',(kw_s_w_type,ykw))
                    return_td['code']=0
                    return_td['err_msg']=None
                    return_td['aff']=aff
                    return_td['kw_s_w_type']=kw_s_w_type
                    break
                except Exception as e:
                    return_td['err_msg']=str(e)
                    return_td['code']=2
                    print(e)
            else:
                return_td['err_msg']='询问AI返回为空'
                return_td['code']=1
        
        return return_td
    rs1=[]

    sql_str0=f'''select top 2000 keyword from keyword_1688 p
                    where p.keyword_type='选品关键词'
                    and size_weight_type is null or size_weight_type =''
                    AND NOT EXISTS (
                        SELECT 1
                        FROM Blacklist b
                        WHERE b.word_language = '英文'
                        AND p.keyword LIKE '%' + b.word_blacklist + '%'
                    )
                    group by keyword
            '''
    rs1.extend(tms.ExecQuery(sql_str0))
    sql_str1=f'''select top 2000 keyword from keyword_1688 p
                    where p.keyword_type='广泛匹配关键词'
                    and size_weight_type is null or size_weight_type =''
                    AND NOT EXISTS (
                        SELECT 1
                        FROM Blacklist b
                        WHERE b.word_language = '中文'
                        AND p.keyword LIKE '%' + b.word_blacklist + '%'
                    )
                    group by keyword
            '''
    rs1.extend(tms.ExecQuery(sql_str1))
    logging.info(f'本次共取出 {len(rs1)} 个关键词,来获取关键词类型')
    with thddd.ThreadPoolExecutor(max_workers=10) as t:
        
        tasks=[]
        for rj,r in enumerate(rs1,start=1):
            keyword=r[0]
            question1=f'''产品:{keyword},
判断上面的产品,体积大小(填大或者小),重量大小(填大或者小),只返回结果就行。
返回格式:{{"体积":"大","重量":"小"}}'''
            
            tasks.append(t.submit(ask_td,rj,keyword,question1))


        for tjjj,t0 in enumerate(thddd.as_completed(tasks),start=1):
            try:
                res=t0.result()
                res_jjj=res['ind']
                res_kw=res['ykw']
                res_aff=res['aff']
                res_err=res['err_msg']
                res_kty=res['kw_s_w_type']
                err_msg=f',{res_err}' if res_err else ''
                logging.info(f'第{res_jjj}个关键词:《{res_kw}》,提取重量体积:{res_kty}{err_msg},影响行数:{res_aff}')
            except Exception as e:
                traceback.print_exc()
                print(f'线程错误 => {e}')

            
            logging.info(f'本次剩余任务队列:{len(rs1)-tjjj}/{len(rs1)}')

def task_extract_1688_product_tags():

    def ask_tag(jjj,pitem,ques):
        json_result=None
        pid,ptit=pitem
        return_td={'code':0,'err_msg':None,'pid':pid,'aff':0,'aff_relation':0,'ind':jjj,'tag_aff_dict':None,'aff_cgstate':0}
        for _ in range(3):
            res_cot=ask_deepseek_json(ques,frequency_penalty=0.1)
            if res_cot:
                try:
                    json_result=json.loads(res_cot)
                    aff=0
                    tag_aff_dict={}
                    for tag_type,tlist in json_result.items():
                        tag_aff_dict[tag_type]=0
                        for tag_name in tlist:
                            tdata={
                                'tag_name':tag_name,
                                'tag_type':tag_type,
                                'tag_source':'AI',
                                'source_product_id':pid
                            }
                            zds=list(tdata.keys())
                            paras=list(tdata.values())
                            aff+=tms.merge_into('tags',zds,paras,['source_product_id','tag_name'],True)
                            tag_aff_dict[tag_type]+=1
                    return_td['aff']=aff
                    aff_relation=0
                    rs_relation=tms.ExecQuery('select tag_id,tag_type,source_product_id from tags where source_product_id=?',(pid,))
                    for cur_tid,cur_tag_type,cur_pid in rs_relation:

                        redata={
                            'source_product_id':cur_pid,
                            'tag_id':cur_tid,
                            'tag_type':cur_tag_type,
                            'tag_source':'AI',
                            'relation_creator':'AI'
                        }
                        zds2=list(redata.keys())
                        paras2=list(redata.values())
                        aff_relation+=tms.merge_into('product_tag_relation',zds2,paras2,['source_product_id','tag_id'],True)

                    
                    return_td['code']=0
                    return_td['err_msg']=None
                    return_td['aff']=aff
                    return_td['aff_relation']=aff_relation
                    return_td['tag_aff_dict']=tag_aff_dict
                    if aff and aff_relation:
                        
                        aff_cgstate=tms.ExecNoQuery('update product_source_info set is_create_tag=1 where source_product_id=?',(pid,))
                        return_td['aff_cgstate']=aff_cgstate
                    break
                except Exception as e:
                    return_td['err_msg']=str(e)
                    return_td['code']=2
                    print(e)
                    continue
            else:
                return_td['err_msg']='询问AI返回为空'
                return_td['code']=1
        
        return return_td
    rs1=[]

    sql_str1=f'select top 2000 source_product_id,title from product_source_info where is_create_tag=0'
    rs1.extend(tms.ExecQuery(sql_str1))
    logging.info(f'本次共取出 {len(rs1)} 个标题,需要提取标签')
    with thddd.ThreadPoolExecutor(max_workers=10) as t:
        
        tasks=[]
        for rj,r in enumerate(rs1,start=1):
            title=r[1]
            question1=f'''{title}
从标题中提取标签，归类到（痛点解决类、产品属性类、适用场景类）
标签名称命名以解决痛点（止汗）出发，而非痛点（出汗）
每个分类可以有多个结果,所有字段值必须使用中文
返回格式:{{
"痛点解决类":["免打扰安装","遮阳防晒","便捷升降"],
"产品属性类":["卷帘","百叶窗帘","升降式"],
"适用场景类":["办公室","卫生间","厨房","窗户"]
}}'''
            
            tasks.append(t.submit(ask_tag,rj,r,question1))


        for tjjj,t0 in enumerate(thddd.as_completed(tasks),start=1):
            try:
                res=t0.result()
                res_jjj=res['ind']
                res_pid=res['pid']
                res_aff=res['aff']
                res_aff2=res['aff_relation']
                res_aff_cgstate=res['aff_cgstate']
                res_tag_aff_dict=res['tag_aff_dict']
                res_err=res['err_msg']
                err_msg=f',{res_err}' if res_err else ''
                logging.info(f'第{res_jjj}个货源产品ID:《{res_pid}》,《tags》更新:{res_aff},《product_tag_relation》更新:{res_aff2}\ntags类型分布:{res_tag_aff_dict},状态更改:{res_aff_cgstate}{err_msg}')
            except Exception as e:
                traceback.print_exc()
                print(f'线程错误 => {e}')

            
            logging.info(f'本次剩余任务队列:{len(rs1)-tjjj}/{len(rs1)}')

def task_translate_1688_product_tags(lg):
    session=requests.session()
    session.headers=DS_Headers
    def ask_tag2(jjj,ctag_name,ques):
        return_td={'code':0,'err_msg':None,'tag_name':ctag_name,'aff':0,'aff_relation':0,'ind':jjj,'fy_res':None,'aff_cgstate':0}
        for _ in range(3):
            res_cot=ask_deepseek(session,ques)
            if res_cot:
                try:
                    aff=0
                    tdata={
                        'tag_name':ctag_name,
                        'language':lg,
                        'translated_text':res_cot
                    }
                    zds=list(tdata.keys())
                    paras=list(tdata.values())
                    aff=tms.merge_into('tags_translation',zds,paras,['tag_name','language'])
                    aff_relation=0
                    rs_relation=tms.ExecQuery('select id,translated_text from tags_translation where tag_name=? and language=?',(ctag_name,lg))
                    if len(rs_relation)>0:
                        tid=rs_relation[0][0]
                        translated_text=rs_relation[0][1]
                        kw_1688_data={
                            'keyword':translated_text,
                            'emarket':'shopee马来',
                            'gender':1,
                            'keyword_type':'选品关键词',
                            'language':lg
                        }
                        zds2=list(kw_1688_data.keys())
                        paras2=list(kw_1688_data.values())
                        aff_relation=tms.merge_into('keyword_1688',zds2,paras2,['keyword','language','emarket'],True)

                        
                        return_td['code']=0
                        return_td['aff']=aff
                        return_td['err_msg']=None
                        return_td['aff_relation']=aff_relation
                        return_td['fy_res']=res_cot
                        if aff and aff_relation:
                            aff_cgstate=tms.ExecNoQuery('update tags_translation set keyword_1688=1 where id=?',(tid,))
                            return_td['aff_cgstate']=aff_cgstate
                        break
                except Exception as e:
                    return_td['err_msg']=str(e)
                    return_td['code']=2
                    print(e)
                    continue
            else:
                return_td['err_msg']='询问AI返回为空'
                return_td['code']=1
        
        return return_td
    rs1=[]
    sql_str1=f'''select t.tag_name from tags t
                    where tag_type='痛点解决类' 
                    and not exists (
                        select 1 from tags_translation f where f.tag_name=t.tag_name and f.language='{lg}'
                    )
                    group by t.tag_type,t.tag_name 
                    having count(*)>=3
                '''
    rs1.extend(tms.ExecQuery(sql_str1))
    logging.info(f'本次共取出 {len(rs1)} 个标签,需要翻译成 {lg}')
    with thddd.ThreadPoolExecutor(max_workers=10) as t:
        
        tasks=[]
        for rj,r in enumerate(rs1,start=1):
            tag_name=r[0]
            question1=f'''{tag_name}
将以上标签翻译成{lg}，电商平台最常用的，只回答结果要求文本格式，无需任何其他文字'''
            
            tasks.append(t.submit(ask_tag2,rj,tag_name,question1))


        for tjjj,t0 in enumerate(thddd.as_completed(tasks),start=1):
            try:
                res=t0.result()
                res_jjj=res['ind']
                res_tag_name=res['tag_name']
                res_aff=res['aff']
                res_aff2=res['aff_relation']
                res_aff_cgstate=res['aff_cgstate']
                res_fy=res['fy_res']
                res_err=res['err_msg']
                err_msg=f',{res_err}' if res_err else ''
                logging.info(f'第{res_jjj}个标签:《{res_tag_name}》,{lg}翻译结果:{res_fy},《tags_translation》更新:{res_aff},《keyword_1688》更新:{res_aff2},状态更改:{res_aff_cgstate}{err_msg}')
            except Exception as e:
                traceback.print_exc()
                print(f'线程错误 => {e}')

            
            logging.info(f'本次剩余任务队列:{len(rs1)-tjjj}/{len(rs1)}')

